<?php

/**
 * Created by PhpStorm.
 * User: nirui
 * Date: 17-7-14
 * Time: 上午11:58
 */
class currentstocks{

    public $title=array(
        'reference_date'=>'日期',
        'sku'=>'SKU',
        'oqs'=>'在途数量',
        'ocs'=>'在途成本',
        'iqs'=>'起初数量',
        'ics'=>'期初成本',
        'wq10'=>'采购入数量',
        'wc10'=>'采购入成本',
        'wq20'=>'订单退回入数量',
        'wc20'=>'订单退回入成本',
        'wq30'=>'盘赢入数量',
        'wc30'=>'盘赢入成本',
        'wq80'=>'订单扫描回库入数量',
        'wc80'=>'订单扫描回库入成本',
        'wq110'=>'供应商换货入数量',
        'wc110'=>'供应商换货入成本',
        'wqother'=>'其他入数量',
        'wcother'=>'其他入成本',
        'dq10'=>'销售出数量',
        'dc10'=>'销售出成本',
        'dq20'=>'盘亏出数量',
        'dc20'=>'盘亏出成本',
        'dq100'=>'调拨出数量',
        'dc100'=>'调拨出成本',
        'dq110'=>'供应商退货出数量',
        'dc110'=>'供应商退货出成本',
        'dq120'=>'供应商换货出数量',
        'dc120'=>'供应商换货出成本',
        'dqother'=>'其他出数量',
        'dcother'=>'其他出成本',
        'iqe'=>'期末库存数量',
        'ice'=>'期末库存成本',
    );
    public $inventories;
    public $onways;
    public $warehouses;
    public $deliveries;
    //5万一次写入
    static $downloadLimitNumber=50000;

    //最大一次下载数量为100万
    static $maxDownloadNumber=1000000;

    /**根据查询条件生成文件名称
     * @param $condition
     */
    static function getFilename(){
        $filename='日结库存报表';
        return $filename;
    }

    /**自动生成下载文件
     * @param $orders 字段顺序
     * @param $filename 文件名称     * @param $head 列名
     * @param $infos 数据
     * @param $continue 续写标志位
     * @return mixed
     */
    static function download($orders,$filename,$head,$infos,$continue=0){
        // 处理头部标题
        $title='';
        foreach($orders as $order){
            $title.=empty($title)?$head[$order]:','.$head[$order];
        }
        $title.=PHP_EOL;
        // 处理内容
        $content = '';
        foreach ($infos as $info) {
            //其他入库数量和成本
            $otherTotalQuantityW=0;
            $otherTotalCostW=0;
            foreach(json_decode($info['warehouses']) as $objw){
                $keys=explode('[]',$objw->skutype);
                if(in_array($keys[1],array(10,20,30,80,110))){
                    continue;
                }
                $otherTotalQuantityW+=$objw->quantity;
                $otherTotalCostW+=$objw->cost;
            }
            //其他出库数量和成本
            $otherTotalQuantityD=0;
            $otherTotalCostD=0;
            foreach(json_decode($info['deliveries']) as $objw){
                $keys=explode('[]',$objw->skutype);
                if(in_array($keys[1],array(10,20,100,110,120))){
                    continue;
                }
                $otherTotalQuantityD+=$objw->quantity;
                $otherTotalCostD+=$objw->cost;
            }
            $singlecontent='';
            $singlecontent.=$info['reference_date']
                .','.(isset($info['sku'])?$info['sku']:'')
                .','.(isset(json_decode($info['onways'])->quantity)?json_decode($info['onways'])->quantity:'')
                .','.(isset(json_decode($info['onways'])->cost)?json_decode($info['onways'])->cost:'')
                .','.(isset(json_decode($info['inventories'])->quantity)?json_decode($info['inventories'])->quantity:'')
                .','.(isset(json_decode($info['inventories'])->cost)?json_decode($info['inventories'])->cost:'')
                .','.(isset(json_decode($info['warehouses'])->{10}->quantity)?json_decode($info['warehouses'])->{10}->quantity:'')
                .','.(isset(json_decode($info['warehouses'])->{10}->cost)?json_decode($info['warehouses'])->{10}->cost:'')
                .','.(isset(json_decode($info['warehouses'])->{20}->quantity)?json_decode($info['warehouses'])->{20}->quantity:'')
                .','.(isset(json_decode($info['warehouses'])->{20}->cost)?json_decode($info['warehouses'])->{20}->cost:'')
                .','.(isset(json_decode($info['warehouses'])->{30}->quantity)?json_decode($info['warehouses'])->{30}->quantity:'')
                .','.(isset(json_decode($info['warehouses'])->{30}->cost)?json_decode($info['warehouses'])->{30}->cost:'')
                .','.(isset(json_decode($info['warehouses'])->{80}->quantity)?json_decode($info['warehouses'])->{80}->quantity:'')
                .','.(isset(json_decode($info['warehouses'])->{80}->cost)?json_decode($info['warehouses'])->{80}->cost:'')
                .','.(isset(json_decode($info['warehouses'])->{110}->quantity)?json_decode($info['warehouses'])->{110}->quantity:'')
                .','.(isset(json_decode($info['warehouses'])->{110}->cost)?json_decode($info['warehouses'])->{110}->cost:'')
                .','.$otherTotalQuantityW
                .','.$otherTotalCostW
                .','.(isset(json_decode($info['deliveries'])->{10}->quantity)?json_decode($info['deliveries'])->{10}->quantity:'')
                .','.(isset(json_decode($info['deliveries'])->{10}->cost)?json_decode($info['deliveries'])->{10}->cost:'')
                .','.(isset(json_decode($info['deliveries'])->{20}->quantity)?json_decode($info['deliveries'])->{20}->quantity:'')
                .','.(isset(json_decode($info['deliveries'])->{20}->cost)?json_decode($info['deliveries'])->{20}->cost:'')
                .','.(isset(json_decode($info['deliveries'])->{100}->quantity)?json_decode($info['deliveries'])->{100}->quantity:'')
                .','.(isset(json_decode($info['deliveries'])->{100}->cost)?json_decode($info['deliveries'])->{100}->cost:'')
                .','.(isset(json_decode($info['deliveries'])->{110}->quantity)?json_decode($info['deliveries'])->{110}->quantity:'')
                .','.(isset(json_decode($info['deliveries'])->{110}->cost)?json_decode($info['deliveries'])->{110}->cost:'')
                .','.(isset(json_decode($info['deliveries'])->{120}->quantity)?json_decode($info['deliveries'])->{120}->quantity:'')
                .','.(isset(json_decode($info['deliveries'])->{120}->cost)?json_decode($info['deliveries'])->{120}->cost:'')
                .','.$otherTotalQuantityD
                .','.$otherTotalCostD
                .','.(isset(json_decode($info['stocks'])->quantity)?json_decode($info['stocks'])->quantity:'')
                .','.(isset(json_decode($info['stocks'])->cost)?json_decode($info['stocks'])->cost:'')
                .PHP_EOL;
            $content.= $singlecontent;
        }

        //文件是否存在，存在判断是否续写，不存在直接写入
        if(file_exists($filename)){
            //判断是否续写，续写不删除文件继续写入，不续写删除文件，重新写入
            if($continue=0){
                unlink ($filename);
                $csv = $title.$content;
            }else{
                $csv = $content;
            }
        }else{
            $csv = $title.$content;
        }
        // 写入并关闭资源
        $fp = fopen($filename,'a');
        fwrite($fp, $csv);
        fclose($fp);
        return $filename;
    }



    /**
     * @param $condition 筛选条件
     * @param $limitsql 分页sql
     * @return mixed 返回符合条件的记录详情（筛选条件包含分页条件）
     * 获取符合筛选条件的记录详情
     */
    public static function getData($condition,$limitsql=''){
        $conditionsql=self::getConditionsql($condition);
        $datas=DB::query(Database::SELECT,"SELECT *
FROM `daylystockinfos`
WHERE 1 ".$conditionsql.$limitsql)->execute('newerp')->as_array();
        return $datas;
    }

    /**
     * @param $condition 查询条件
     * @return string 返回查询条件sql
     */
    public static function getConditionsql($condition){
        $conditionsql='';

        if(isset($condition['reference_date'])&&$condition['reference_date']!=''){
            $conditionsql.=" AND `reference_date`='".$condition['reference_date']."'";
        }
        if(isset($condition['sku'])&&$condition['sku']!=''){
            $conditionsql.=" AND `sku`='".$condition['sku']."'";
        }
        return $conditionsql;
    }

    /**
     * @param $condition 筛选条件
     * @return mixed 返回符合筛选条件的记录数量
     * 计数方法，统计符合筛选条件的记录条数
     */
    public static function countData($condition){
        $conditonsql=self::getConditionsql($condition);
        $datas=DB::query(
            Database::SELECT,
            'SELECT COUNT(1) `countN`
FROM `daylystockinfos`
WHERE 1 '.$conditonsql
        )
            ->execute('newerp')
            ->get('countN');
        return $datas;
    }

    public function createBaseData($date){
        $date=isset($date)?$date:date('Y-m-d');
        $deleteNumber=DB::query(Database::DELETE,'DELETE FROM `daylystockinfos` WHERE `reference_date`="'.$date.'"')
        ->execute('newerp');
        echo "日期：".$date."清除库存记录，共计 ".$deleteNumber."条<br/>";

        $warehouseids=disaster::getWarehouseidByStoreid(0);

        //设置计算开始时间：$date为一号时，计算时间为上个月月初，其他时间为$date月份的一号
        if(strpos($date,'-01')===7){
            $datestart=date('Y-m-01',strtotime("-1 month $date"));
        }else{
            $datestart=date('Y-m-01',strtotime("$date"));
        }

        //sku 期初数据获取
        $this->inventories=DB::query(Database::SELECT,'SELECT `sku`,SUM(`quantity`) `quantity`,(`cost`) `cost`
FROM `inventories`
WHERE `inventory_date`="'.$datestart.'"
AND `warehouseid` IN('.implode(',',array_keys($warehouseids)).')
AND (`quantity`!=0 OR `cost`!=0)
GROUP BY `sku`')->execute('newerp')->as_array('sku');

        //sku 采购在途数据获取
        $this->onways=DB::query(Database::SELECT,'SELECT pd.`sku` `sku`,SUM(pd.`quantity`) `quantity`,SUM(pd.`money`) `cost`
FROM `purchaseorder_details` pd
LEFT  JOIN  `purchaseorders` p
ON p.`id`=pd.`purchaseorder_id`
WHERE p.`status`>=20
AND p.`status`<70
AND pd.`status`>=20
AND pd.`status`<70
AND p.`warehouseid` IN('.implode(',',array_keys($warehouseids)).')
GROUP BY `sku`')->execute('newerp')->as_array('sku');

        //sku从月初到$date为止的出库
        $this->deliveries=DB::query(Database::SELECT,'SELECT CONCAT_WS("[]",`sku`,`type`) `skutype`,SUM(`quantity`) `quantity`,SUM(`cost`) `cost`
FROM `deliveryorders`
WHERE `delivery_date`>="'.$datestart.'"
AND `delivery_date`<"'.$date.'"
AND `warehouseid` IN('.implode(',',array_keys($warehouseids)).')
GROUP BY CONCAT_WS("[]",`sku`,`type`)')->execute('newerp')->as_array('skutype');
        $skudeliveries=array();
        foreach($this->deliveries as $skutype=>$delivery){
            $keys=explode('[]',$skutype);
            $skudeliveries[$keys[0]][$keys[1]]=$delivery;
        }

        //sku从月初到$date为止的入库
        $this->warehouses=DB::query(Database::SELECT,'SELECT CONCAT_WS("[]",`sku`,`type`) `skutype`,SUM(`warehouse_quantity`) `quantity`,SUM(`cost`) `cost`
FROM `warehouseorders`
WHERE `warehouse_date`>="'.$datestart.'"
AND `warehouse_date`<"'.$date.'"
AND `warehouseid` IN('.implode(',',array_keys($warehouseids)).')
GROUP BY CONCAT_WS("-",`sku`,`type`)')->execute('newerp')->as_array('skutype');
        $skuwarehouses=array();
        foreach($this->warehouses as $skutype=>$warehouse){
            $keys=explode('[]',$skutype);
            $skuwarehouses[$keys[0]][$keys[1]]=$warehouse;
        }

        //获取所有产生出入库或库存数量成本不为0的sku
        $skus=array_unique(
            array_merge(array_keys($this->inventories)
                ,array_keys($this->onways)
                ,array_keys($skudeliveries)
                ,array_keys($skuwarehouses)));

        //sku当天凌晨的实时库存
        $stocks=DB::query(Database::SELECT,'SELECT a.`sku` `sku`,SUM(a.`quantity`) `quantity`,SUM(a.`cost`) `cost`
FROM
(
    SELECT `sku`,`quantity`,`cost`
    FROM `inventories`
    WHERE `inventory_date`="'.$datestart.'"
    AND `warehouseid` IN('.implode(',',array_keys($warehouseids)).')
    AND `sku` IN ("'.implode('","',$skus).'")
    UNION ALL
    SELECT `sku`,`warehouse_quantity` `quantity`,`cost`
    FROM `warehouseorders`
    WHERE `warehouse_date`>="'.$datestart.'"
    AND `warehouse_date`<"'.$date.'"
    AND `warehouseid` IN('.implode(',',array_keys($warehouseids)).')
    AND `sku` IN ("'.implode('","',$skus).'")
    UNION ALL
    SELECT `sku`,(-`quantity`) `quantity`,(-`cost`) `cost`
    FROM `deliveryorders`
    WHERE `delivery_date`>="'.$datestart.'"
    AND `delivery_date`<"'.$date.'"
    AND `warehouseid` IN('.implode(',',array_keys($warehouseids)).')
    AND `sku` IN ("'.implode('","',$skus).'")
) a
GROUP BY a.`sku`')->execute('newerp')->as_array('sku');

        $sql="INSERT INTO `daylystockinfos`
(`sku`, `reference_date`, `inventories`, `onways`, `stocks`, `warehouses`, `deliveries`) VALUES ";
        $valuesql='';
        $count=0;//数据库插入计数器，1000条执行一次插入重置为0
        $long=0;//循环次数计数器，最后一次循环等于sku数组的长度，执行插入后跳出
        $insertTotalCount=0;
        foreach($skus as $sku){

            $long++;
            //如果该sku期初，在途，前一天出入库记录都不存在，就跳过
            if(!isset($this->inventories)
                &&!isset($this->onways[$sku])
                &&!isset($skuwarehouses[$sku])
                &&!isset($skudeliveries[$sku])){
                continue;
            }
            $inventory=isset($this->inventories[$sku])?json_encode($this->inventories[$sku]):'';
            $onway=isset($this->onways[$sku])?json_encode($this->onways[$sku]):'';
            $stock=isset($stocks[$sku])?json_encode($stocks[$sku]):'';
            $warehouse=isset($skuwarehouses[$sku])?json_encode($skuwarehouses[$sku]):'';
            $delivery=isset($skudeliveries[$sku])?json_encode($skudeliveries[$sku]):'';

            //添加值条件
            $valuesql.=empty($valuesql)?'':',';
            $valuesql.="('".$sku."'
            ,'".$date."'
            ,'".$inventory."'
            ,'".$onway."'
            ,'".$stock."'
            ,'".$warehouse."'
            ,'".$delivery."')";
            $count++;

            //1000条记录执行一次插入，插入后设置计数为0，值条件为空
            if($count==2000||$long==count($skus)){
                $insertnumber=DB::query(Database::INSERT,$sql.$valuesql)->execute('newerp');
                $insertTotalCount+=$insertnumber[1];
                $count=0;
                $valuesql='';
            }
        }
        echo "日期：".$date."插入数据，共计 ".$insertTotalCount."条<br/>";
    }
}